﻿using System;
using System.Data;
using System.Text;
using System.Data.SqlClient;
using Youz.Common.DBUtility;
using Youz.Common.Entity;

namespace Youz.Common.DAL
{
    /// <summary>
    /// 数据访问类:Sys_Holiday
    /// </summary>
    public partial class Sys_HolidayDAL
    {

        private DbHelperSQL DbHelperSQL = new DbHelperSQL();
        public string TableName = "Sys_Holiday";
        public Sys_HolidayDAL()
        { }

        #region  BasicMethod

        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(DateTime HolidayDate)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from Sys_Holiday");
            strSql.Append(" where HolidayDate=@HolidayDate ");
            SqlParameter[] parameters = {
					new SqlParameter("@HolidayDate", SqlDbType.DateTime)			
                                        };
            parameters[0].Value = HolidayDate;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }


        /// <summary>
        /// 增加一条数据
        /// </summary>
        public bool Add(Youz.Common.Entity.Sys_Holiday model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into Sys_Holiday(");
            strSql.Append("HolidayID,HolidayTitle,HolidayDate,Status,Memo,Creator,CreateTime)");
            strSql.Append(" values (");
            strSql.Append("@HolidayID,@HolidayTitle,@HolidayDate,@Status,@Memo,@Creator,@CreateTime)");
            SqlParameter[] parameters = {
					new SqlParameter("@HolidayID", SqlDbType.UniqueIdentifier,16),
					new SqlParameter("@HolidayTitle", SqlDbType.NVarChar,100),
					new SqlParameter("@HolidayDate", SqlDbType.DateTime),
					new SqlParameter("@Status", SqlDbType.Int,4),
					new SqlParameter("@Memo", SqlDbType.NVarChar,100),
					new SqlParameter("@Creator", SqlDbType.NVarChar,100),
					new SqlParameter("@CreateTime", SqlDbType.DateTime)};
            parameters[0].Value = Guid.NewGuid();
            parameters[1].Value = model.HolidayTitle;
            parameters[2].Value = model.HolidayDate;
            parameters[3].Value = model.Status;
            parameters[4].Value = model.Memo;
            parameters[5].Value = model.Creator;
            parameters[6].Value = model.CreateTime;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(Youz.Common.Entity.Sys_Holiday model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update Sys_Holiday set ");
            strSql.Append("HolidayTitle=@HolidayTitle,");
            strSql.Append("HolidayDate=@HolidayDate,");
            strSql.Append("Status=@Status,");
            strSql.Append("Memo=@Memo,");
            strSql.Append("Creator=@Creator,");
            strSql.Append("CreateTime=@CreateTime,");
            strSql.Append("LastModifier=@LastModifier,");
            strSql.Append("LastModifyTime=@LastModifyTime");
            strSql.Append(" where HolidayID=@HolidayID ");
            SqlParameter[] parameters = {
					new SqlParameter("@HolidayTitle", SqlDbType.NVarChar,100),
					new SqlParameter("@HolidayDate", SqlDbType.DateTime),
					new SqlParameter("@Status", SqlDbType.Int,4),
					new SqlParameter("@Memo", SqlDbType.NVarChar,100),
					new SqlParameter("@Creator", SqlDbType.NVarChar,100),
					new SqlParameter("@CreateTime", SqlDbType.DateTime),
					new SqlParameter("@LastModifier", SqlDbType.NVarChar,100),
					new SqlParameter("@LastModifyTime", SqlDbType.DateTime),
					new SqlParameter("@HolidayID", SqlDbType.UniqueIdentifier,16)};
            parameters[0].Value = model.HolidayTitle;
            parameters[1].Value = model.HolidayDate;
            parameters[2].Value = model.Status;
            parameters[3].Value = model.Memo;
            parameters[4].Value = model.Creator;
            parameters[5].Value = model.CreateTime;
            parameters[6].Value = model.LastModifier;
            parameters[7].Value = model.LastModifyTime;
            parameters[8].Value = model.HolidayID;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public int GetValidDays(DateTime startDate, DateTime endDate)
        {
            string strSql = "select [dbo].[F_GetValidityDate](@StartDate, @EndDate)";
            SqlParameter[] parameters = {
					new SqlParameter("@StartDate", startDate),
                    new SqlParameter("@EndDate", endDate)};

            IDataReader dr = DbHelperSQL.ExecuteReader(strSql, parameters);

            if (dr != null)
            {
                if (dr.Read())
                {
                    return (int)dr[0];
                }
            }

            return 0;
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(Guid HolidayID)
        {
            string strSql = "delete from Sys_Holiday where HolidayID=@HolidayID ";
            SqlParameter[] parameters = {
					new SqlParameter("@HolidayID", SqlDbType.UniqueIdentifier,16)			
                                        };
            parameters[0].Value = HolidayID;

            int rows = DbHelperSQL.ExecuteSql(strSql, parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 批量删除数据
        /// </summary>
        public bool DeleteList(string HolidayIDlist)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from Sys_Holiday ");
            strSql.Append(" where HolidayID in (" + HolidayIDlist + ")  ");
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString());
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public Youz.Common.Entity.Sys_Holiday GetModel(Guid HolidayID)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 HolidayID,HolidayTitle,HolidayDate,Status,Memo,Creator,CreateTime,LastModifier,LastModifyTime from Sys_Holiday ");
            strSql.Append(" where HolidayID=@HolidayID ");
            SqlParameter[] parameters = {
					new SqlParameter("@HolidayID", SqlDbType.UniqueIdentifier,16)			};
            parameters[0].Value = HolidayID;

            Youz.Common.Entity.Sys_Holiday model = new Youz.Common.Entity.Sys_Holiday();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                return DataRowToModel(ds.Tables[0].Rows[0]);
            }
            else
            {
                return null;
            }
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public Youz.Common.Entity.Sys_Holiday DataRowToModel(DataRow row)
        {
            Youz.Common.Entity.Sys_Holiday model = new Youz.Common.Entity.Sys_Holiday();
            if (row != null)
            {
                if (row["HolidayID"] != null && row["HolidayID"].ToString() != "")
                {
                    model.HolidayID = new Guid(row["HolidayID"].ToString());
                }
                if (row["HolidayTitle"] != null)
                {
                    model.HolidayTitle = row["HolidayTitle"].ToString();
                }
                if (row["HolidayDate"] != null && row["HolidayDate"].ToString() != "")
                {
                    model.HolidayDate = DateTime.Parse(row["HolidayDate"].ToString());
                }
                if (row["Status"] != null && row["Status"].ToString() != "")
                {
                    model.Status = int.Parse(row["Status"].ToString());
                }
                if (row["Memo"] != null)
                {
                    model.Memo = row["Memo"].ToString();
                }
                if (row["Creator"] != null)
                {
                    model.Creator = row["Creator"].ToString();
                }
                if (row["CreateTime"] != null && row["CreateTime"].ToString() != "")
                {
                    model.CreateTime = DateTime.Parse(row["CreateTime"].ToString());
                }
                if (row["LastModifier"] != null)
                {
                    model.LastModifier = row["LastModifier"].ToString();
                }
                if (row["LastModifyTime"] != null && row["LastModifyTime"].ToString() != "")
                {
                    model.LastModifyTime = DateTime.Parse(row["LastModifyTime"].ToString());
                }
            }
            return model;
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            string strSql = @"select HolidayID,HolidayTitle,HolidayDate
                          from Sys_Holiday";
            if (strWhere.Trim() != "")
            {
                strSql += " where " + strWhere;
            }
            return DbHelperSQL.Query(strSql);
        }

        /// <summary>
        /// 获得前几行数据
        /// </summary>
        public DataSet GetList(int Top, string strWhere, string filedOrder)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (Top > 0)
            {
                strSql.Append(" top " + Top.ToString());
            }
            strSql.Append(" HolidayID,HolidayTitle,HolidayDate,Status,Memo,Creator,CreateTime,LastModifier,LastModifyTime ");
            strSql.Append(" FROM Sys_Holiday ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by " + filedOrder);
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获取记录总数
        /// </summary>
        public int GetRecordCount(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) FROM Sys_Holiday ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            object obj = DbHelperSQL.GetSingle(strSql.ToString());
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }
        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        public DataSet GetListByPage(string strWhere, string orderby, int startIndex, int endIndex)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT * FROM ( ");
            strSql.Append(" SELECT ROW_NUMBER() OVER (");
            if (!string.IsNullOrEmpty(orderby.Trim()))
            {
                strSql.Append("order by T." + orderby);
            }
            else
            {
                strSql.Append("order by T.HolidayID desc");
            }
            strSql.Append(")AS Row, T.*  from Sys_Holiday T ");
            if (!string.IsNullOrEmpty(strWhere.Trim()))
            {
                strSql.Append(" WHERE " + strWhere);
            }
            strSql.Append(" ) TT");
            strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);
            return DbHelperSQL.Query(strSql.ToString());
        }

        /*
        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        public DataSet GetList(int PageSize,int PageIndex,string strWhere)
        {
            SqlParameter[] parameters = {
                    new SqlParameter("@tblName", SqlDbType.VarChar, 255),
                    new SqlParameter("@fldName", SqlDbType.VarChar, 255),
                    new SqlParameter("@PageSize", SqlDbType.Int),
                    new SqlParameter("@PageIndex", SqlDbType.Int),
                    new SqlParameter("@IsReCount", SqlDbType.Bit),
                    new SqlParameter("@OrderType", SqlDbType.Bit),
                    new SqlParameter("@strWhere", SqlDbType.VarChar,1000),
                    };
            parameters[0].Value = "Sys_Holiday";
            parameters[1].Value = "HolidayID";
            parameters[2].Value = PageSize;
            parameters[3].Value = PageIndex;
            parameters[4].Value = 0;
            parameters[5].Value = 0;
            parameters[6].Value = strWhere;	
            return DbHelperSQL.RunProcedure("UP_GetRecordByPage",parameters,"ds");
        }*/

        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        public DataTable GetPager(int pageIndex, int pageSize, string holidayTitle, string holidayDate, out int total)
        {
            string sql = "select HolidayID, HolidayTitle,Status, CONVERT(varchar(100), HolidayDate, 23) as HolidayDate, [CreateTime] from Sys_Holiday where 1=1";

            if (!string.IsNullOrEmpty(holidayTitle))
            {
                sql += " and HolidayTitle like '%" + holidayTitle + "%'";
            }
            if (!string.IsNullOrEmpty(holidayDate))
            {
                sql += " and HolidayDate = '" + holidayDate + "'";
            }

            return DbHelperSQL.GetPager(pageIndex, pageSize, sql, "ORDER BY HolidayDate desc", out total);
        }

        #endregion  BasicMethod
        #region  ExtensionMethod
        /// <summary>
        /// 判断是否为节假日
        /// </summary>
        /// <returns></returns>
        public DataTable IsHoliday(DateTime SysTime)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"declare @num int

                            select  @num=COUNT(-1) from Sys_Holiday where HolidayDate=CONVERT(varchar(100),@Time, 23)
                                if(@num>0)
                                        begin
                                            select 'true' as IsHoliday
                                        end
                                else 
                                    begin
                                      if datepart(dw,@Time)=1 
                                        select 'true' as IsHoliday
                                      else 
                                        select 'false' as IsHoliday 
                                      end ");

            SqlParameter[] parameters = {
                  
                    new SqlParameter("@Time", SysTime)
            };
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            return ds.Tables[0];
        }

        public Sys_Holiday Get(DateTime HolidayDate)
        {
            DataSet ds = DbHelperSQL.Query("select * from Sys_Holiday where HolidayDate=@HolidayDate", new SqlParameter[]{ new SqlParameter("@HolidayDate", HolidayDate) });

            if (ds.Tables[0].Rows.Count > 0)
            {
                return DataRowToModel(ds.Tables[0].Rows[0]);
            }
            else
            {
                return null;
            }
        }
        #endregion  ExtensionMethod
    }
}

